﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Web.Configuration;
using System.Data.SqlClient;

/// <summary>
/// Summary description for CusManagement
/// </summary>
public class CusManagement
{
    #region const declaration
    public const string TABLE_NAME = "Customers";
    public const string CUSID = "cusID";
    public const string CUSNAME = "cusName";
    public const string TEL = "tel";
    public const string EMAIL = "email";
    #endregion


	public CusManagement()
	{
	}

    public static int AddCus(string cusName, string tel, string email)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "INSERT INTO Customers Values(@cusName,@tel,@email);SELECT scope_identity()";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@cusName", cusName);
        command.Parameters.AddWithValue("@tel", tel);
        command.Parameters.AddWithValue("@email", email);

        int n = int.Parse(command.ExecuteScalar().ToString());

        cnn.Close();

        return n;
    }

    public static bool EditCus(int cusID, string newCusName, string newTel, string newEmail)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "UPDATE Customers Set cusName =@newCusName,Tel=@newTel,email=@newEmail WHERE cusID=@cusID";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@newCusName", newCusName);
        command.Parameters.AddWithValue("@newTel", newTel);
        command.Parameters.AddWithValue("@newEmail", newEmail);
        command.Parameters.AddWithValue("@cusID", cusID);

        int n = command.ExecuteNonQuery();

        cnn.Close();

        if (n == 1)
            return true;
        else
            return false;
    }

    public static bool DeleteCus(int cusID)
    {
        //Open connect to database:
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "DELETE Customers WHERE cusID=@cusID";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@cusID", cusID);

        int n = command.ExecuteNonQuery();

        if (n == 0)
            return false;
        else
            return true;
    }

    public static DataSet GetAllCus()
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);

        //string sqlQuery = "SELECT * FROM Customers ORDER BY cusName";
        string sqlQuery = "SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY cusName) AS NO, C.* FROM Customers C) AS SUB_SQL";
        SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, cnn);

        DataSet dsAllCus = new DataSet();
        adapter.Fill(dsAllCus, "AllCus");

        return dsAllCus;
    }

    public static DataTable GetCusByID(int cusID)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);

        string sqlQuery = "SELECT * FROM Customers C WHERE C.cusID=" + cusID;
        SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, cnn);

        DataSet dsCusByID = new DataSet();
        adapter.Fill(dsCusByID, "CusByID");

        return dsCusByID.Tables["CusByID"];
    }
}